package com.wuyou.service;


import cn.hutool.core.util.StrUtil;
import com.wuyou.entity.Table;
import com.wuyou.entity.TableColumn;
import com.wuyou.param.ExportSqlTableToWordParam;
import com.wuyou.util.ExportWordUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.xwpf.usermodel.XWPFDocument;
import org.apache.poi.xwpf.usermodel.XWPFParagraph;
import org.apache.poi.xwpf.usermodel.XWPFRun;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.util.List;
import java.util.Map;
import java.util.StringJoiner;
import java.util.stream.Collectors;

@Slf4j
@Service("exportSqlToWordService")
public class ExportSqlToWordService {

    final private JdbcTemplate jdbcTemplate;

    public ExportSqlToWordService(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 查询表sql
     */
    private final static String QUERY_TABLE_SQL = "select TABLE_NAME tableName ,TABLE_COMMENT tableComment  from INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '{}' ORDER BY TABLE_NAME";

    /**
     * 查询表字段数据
     */
    private final static String QUERY_COLUMN_SQL = "SELECT\n" +
            "\tTABLE_NAME tableName,\n" +
            "\tCOLUMN_NAME columnName,\n" +
            "\tCOLUMN_TYPE columnType,\n" +
            "\tCOLUMN_COMMENT columnComment,\n" +
            "IF\n" +
            "\t(\n" +
            "\t\tCOLUMN_KEY IS NOT NULL \n" +
            "\t\tAND COLUMN_KEY != '',\n" +
            "\tCASE\n" +
            "\t\t\tCOLUMN_KEY \n" +
            "\t\t\tWHEN 'PRI' THEN\n" +
            "\t\t\t'PRIMARY KEY' \n" +
            "\t\t\tWHEN 'MUL' THEN\n" +
            "\t\t\t'' ELSE COLUMN_KEY \n" +
            "\t\tEND,\n" +
            "\tCASE\n" +
            "\t\t\tIS_NULLABLE \n" +
            "\t\t\tWHEN 'NO' THEN\n" +
            "\t\t\t'NOT NULL' ELSE '' \n" +
            "\t\tEND \n" +
            "\t\t) AS constraintCondition \n" +
            "\tFROM\n" +
            "\t\tINFORMATION_SCHEMA.COLUMNS \n" +
            "\tWHERE\n" +
            "\t\tTABLE_SCHEMA = '{}' \n" +
            "\t\t and TABLE_NAME IN ({}) \n" +
            "\tORDER BY\n" +
            "\tTABLE_NAME,\n" +
            "\tORDINAL_POSITION";

    // 定义 RowMapper 用于将结果集映射为实体对象
    RowMapper<TableColumn> rowMapper = (resultSet, rowNum) -> {
        TableColumn entity = new TableColumn();
        entity.setTableName(resultSet.getString("tableName"));
        entity.setColumnComment(resultSet.getString("columnComment"));
        entity.setColumnType(resultSet.getString("columnType"));
        entity.setColumnName(resultSet.getString("columnName"));
        entity.setConstraintCondition(resultSet.getString("constraintCondition"));
        return entity;
    };

    RowMapper<Table> tableMapper = (resultSet, rowNum) -> {
        Table entity = new Table();
        entity.setTableName(resultSet.getString("tableName"));
        entity.setTableComment(resultSet.getString("tableComment"));
        return entity;
    };

    /**
     * 表头
     */
    private final static String[] TABLE_CELL_NAME = new String[]{"列名","数据类型","约束条件","说明"};


    /**
     * 表字段map
     * @param param 请求参数
     */
    private Map<String, List<TableColumn>> getTableColumnsMap(ExportSqlTableToWordParam param) {
        List<String> tableName = param.getTableName();
        List<String> collect = tableName.stream().map(e -> "'" + e + "'").collect(Collectors.toList());
        StringJoiner sj = new StringJoiner(",");
        for(String s : collect){
            sj.add(s);
        }
        String format = StrUtil.format(QUERY_COLUMN_SQL, param.getDatabase(), sj.toString());
        List<TableColumn> list = jdbcTemplate.query(format, rowMapper);
        return list.stream()
                .collect(Collectors.groupingBy(TableColumn::getTableName));
    }

    /**
     * {表明：表描述}
     * @param param 请求参数
     */
    private Map<String, String> getTableMap(ExportSqlTableToWordParam param) {
        String format = StrUtil.format(QUERY_TABLE_SQL, param.getDatabase());
        List<Table> list = jdbcTemplate.query(format, tableMapper);
        return list.stream()
                .collect(Collectors.toMap(Table::getTableName, Table::getTableComment));
    }

    /**
     * word文档的地址
     */
    public final static String DOC_FILE = "D:\\Documents\\Desktop\\tableList.docx";

    public void exportSqlToWord(ExportSqlTableToWordParam param) {
        Map<String, List<TableColumn>> map = getTableColumnsMap(param);
        Map<String, String> tableMap = getTableMap(param);
        for (String tableName : map.keySet()) {
            List<TableColumn> columns = map.get(tableName);
            writeWord(tableName,tableMap.get(tableName), columns);
        }
    }

    /**
     * 写入word表数据
     * @param tableName 表名
     * @param tableComment 表描述
     * @param columns 表字段
     */
    private void writeWord(String tableName,String tableComment, List<TableColumn> columns) {
        try {
            // 打开现有的Word文档
            // 创建文档对象
            XWPFDocument document = new XWPFDocument();

            File file = new File(DOC_FILE);
            boolean appendFlag = file.exists();
            if (!file.exists()) {
                log.info("文件不存在，需要新建");
            } else {
                FileInputStream fis = new FileInputStream(DOC_FILE);
                document = new XWPFDocument(fis);
            }

            double textSize = 10.5,
                    twoTextSize = 11;

            if (appendFlag) {
                // 空格
                ExportWordUtil.writeLine(document,"\n", null, textSize);
            }

            XWPFParagraph paragraph = document.createParagraph();
            XWPFRun run = paragraph.createRun();
            run.setText("表名：");
            run.setBold(true);
            run.setFontSize(twoTextSize);

            XWPFRun run2 = paragraph.createRun();
            run2.setText(tableName);
            run2.setFontSize(twoTextSize);

            XWPFRun run4 = paragraph.createRun();
            run4.setText(tableComment);
            run4.setFontSize(twoTextSize);

            String[][] strings = new String[columns.size()][4];
            for (int i = 0; i < columns.size(); i++) {
                TableColumn tableColumn = columns.get(i);
                strings[i] = new String[] {
                    tableColumn.getColumnName(),
                    tableColumn.getColumnType(),
                    tableColumn.getConstraintCondition(),
                    tableColumn.getColumnComment()
                };
            }
            Boolean[] booleans = {false, true, true, false};
            ExportWordUtil.addTable(strings, document, TABLE_CELL_NAME, booleans);

            // 保存文档
            FileOutputStream out = new FileOutputStream(DOC_FILE);
            document.write(out);
            out.close();
            document.close();
            log.info("Word文档生成成功！table={}", tableName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}
